---

PowerBase

Derek Haslam introduces the latest changes to Powerbase.

Powerbase 9.20DA is the latest version of what was first introduced in March 2002 under the provisional name PbaseDA. It is compatible with all versions of RISC OS from 3.5 to 4.37 Since it uses dynamic areas it will not work with RISC OS 3.1 - but v.9.20SH will. The two versions are functionally identical and have many features which are not present in earlier versions and these differences are described on this page. All new topics are described in the latest version of the User Manual which has been completely updated to cover these features. The new manual, together with an expanded version of the Tutorials manual, is available for download in Impression Publisher, Ovation Pro, PDF and HTML formats.

The following descriptions of new features are extracted from the latest version of the user manual.

Altering the record format

Version 9.20 of Powerbase introduces far-reaching changes in the way the record format is edited. Earlier versions caused confusion in the minds of many users by having two choices on the Utilities submenu which did similar things. Adjust format enabled minor changes such as moving fields about in the window, renaming fields and the addition or removal of External, Button, and Scrolling list fields. New record format was needed in order to make any change which required the Database file to be rebuilt, i.e. addition or removal of fields (other than those mentioned above), changing the length of fields, and swapping or renumbering fields. Users no longer need to decide whether an alteration will require a rebuild of the Database file since Powerbase can now make such a decision by itself. The two choices have therefore been replaced by a single menu choice: Alter format.

The process has been improved in other ways too. After changing the structure using older versions it was often found that self-calculating fields no longer worked until their formulae had been re-entered. Users were warned against changing both the tag and number of a field (otherwise data could end up in the wrong field). Sometimes indexes needed rebuilding. Under v.9.20 these annoyances should not occur. Close attention has been paid to providing the means to undo format alterations and revert to the database as it was before the changes were made (although common prudence suggests that you should always keep a backup copy of any database on an alternative medium such as another hard drive, a floppy disc or a CD ROM).

The best way to acquaint yourself with the way things work is to experiment. Suppose we have a database called !Addresses. When you choose Alter format from the iconbar Utilities submenu the following things happen:

• An ordinary directory called Addresses! is created (unless one exists already). This unusual naming convention avoids possible clashes with any pre-existing directory or file called Addresses. The new directory is created in the same directory as the database !Addresses.
• An application directory called !Undo01 is created inside Addresses! This initially contains a !Run file and a copy of the Form file from !Addresses. If the database uses validation tables there will also be a copy of the Link file. If Computed fields (or certain other specialised types) are involved then the Calc file is copied too. Many simple databases do not have either a Link or a Calc file. Other objects may be added later.
• The record window is displayed in "design" mode with the blue layout grid.
• A message explains what to do next and informs you that the originals of any altered files will be placed in !Undo01.

During the foregoing activity Powerbase will have been keeping track of certain things which it needs to know so that the changes can, if necessary, be undone and it will now inform you of changes which might result in the modified database containing less data that the original. Deleted fields are an obvious example, but you will also be warned about shortened fields and Scrollable lists with fewer or narrower columns than in the original. If you choose to proceed despite these warnings the restructuring will take place and you should end up with exactly what you asked for but the !Undo01 directory will now contain copies of any files which have been changed by the reformatting. The addition to !Undo01 which you will most commonly see is a copy of your original Database file, but there might also be copies of the Indexes directory or of directories holding External field and Scrollable list data.

One file which definitely will be there is an Obey file called !Restore. You might like to look at this very important file, which contains the instructions for putting everything back as it was.

Wish you�d left well alone? Want to go back? Just double-click on !Undo01. All your renamings will be reversed and all the saved original files copied back. The restored database, which should look exactly as it did when you started, is then re-opened for you and !Undo01 finally deletes itself!

Why is the "undo" application called !Undo01, not simply !Undo? You will see why if you start another session of alterations beginning where you previously left off, that is by again choosing Alter format without having run !Undo01 which is still sitting there in the Addresses! directory. A new application directory called !Undo02 is created. A third alteration session will result in !Undo03 and so on. This allows you to change your databases a little at a time, assessing the effect of each alteration before proceeding to the next. The most recent alteration session is undone by running the highest-numbered undo application, the penultimate session is undone by running the next-highest etc. If, after a lengthy series of alterations, you want to go right back to the beginning you should run the undo applications in reverse order. Do not simply run !Undo01 because it only "knows" about what you did in the first session and the database structure might by now be very different.

How many undo applications can you have? This depends on the configured maximum for auto-generated filenames. The value will be found in the Config file beside the token MaxNames and the default is 10. After !Undo10 you will be informed that this is the highest-numbered name and subsequent sessions will use (and keep overwriting) !Undo. Deleting some or all of the existing undo applications (starting with the highest-numbered) allows auto-name-generation to resume.

Subsidiary indexes and printing speed

In many cases Powerbase can make use of subsidiary indexes to make reports complete much more quickly. This feature has existed for a long time but was, prior to v.9.20, very restricted. It has now been greatly extended and requires a detailed explanation. You might first like to try the following simple exercise with the Elements database.

Create an index on the Group (GP) field, select the element name only, and create a report using the search formula:

If you do this first with the Indexes button on the Query panel selected (default), then with it deselected, you will notice a difference between the times taken. Use ADJUST, not SELECT to click the Print button on the Match window so that the window remains open and you can see the actual times. On a StrongArm RiscPC the above test takes about 0.25 sec with Indexes selected and about twice as long with it deselected.

This might not sound like a difference worth bothering about, but remember that Elements is a very small database; you are printing 31 elements out of only 103. If you were targeting a similar number in a database containing thousands of records the difference would be very marked indeed; a factor of 10 is common and a factor of 50 or more might be achieved in some instances.

Here�s what happens in that example. Powerbase recognizes that GP is indexed so, instead of going through all the records, it jumps straight to the first occurrence of "T" in the subsidiary index and prints records sequentially until the key is no longer "T". There is one small disadvantage which becomes obvious if you try something like GP=1,2,3 which is equivalent to GP=1 OR GP=2 OR GP=3. The names aren�t all in alphabetical order, which they will be if you repeat the exercise with Indexes deselected. The group 1 elements are found first and duly listed in alphabetical order (caesium to sodium). Group 2 element (barium to strontium) then follow and, finally, group 3 elements (aluminum to thallium). You can, of course, click MENU over the list and sort the names so it�s not much of a disadvantage, but attention is drawn to this point because there may be situations where you want to avoid it by deselecting Indexes.

Indexes can not be used in the way described when:

• a hyphen is used to specify a set of contiguous fields (tagX-tagY=target)
• the comparator between tag and target is anything other than =, >, >=, <, <=.
  You can�t use {, }{ or <>.
• the contents of validation tables are being interrogated (see 5.9)
• search elements linked by OR involve a non-indexed field. (It makes no difference whether the OR is explicitly stated or implied, i.e. by using a comma-separated list of field tags.)

To clarify the last case (above), consider two fields, F1 and F2, both of which are indexed, the indexes being numbered 1 and 2 respectively. The search formula:

will first search index 1 for all occurrences of "wibble" in F1, then search index 2 for all occurrences in F2. If F2 is not indexed Powerbase has to search the whole database for occurrences of "wibble" in that field, so the use of an index for the other field is of no help at all. No such problem would occur if the search formula were:

because all records in which F2 is matched must also be ones in which F1 is matched, so index 1 would be used.

For indexes to be used as described they must meet the following criteria:

• If the index uses a multi-part key the field being tested must provide the first part.
• The characters used in the key must come from the first word of the field.
• The characters must come from the left of the word.

Testing validation table contents in search formulae

v.9.20 supports an extension to the search formula syntax which enables tests on validated fields to operate on table columns other than the one linked to the field. The following, trivial example will help to clarify the concept. In the Elements database the field GP is linked to column 0 of a validation table called Group. Column 1 of this table holds the names of the periodic groups, e.g. "Halogen" for group 7. Consider the search formula:

The "/1" suffix to GP tells Powerbase not to test the actual value present in GP but, instead, to find that value in the linked column (column 0) of the table and look at whatever is on the same row in column 1 to see if it matches the string "Halogen".

A report produced using the above formula would contain exactly the same records as one produced simply with GP=7 because there's a straightforward one-to-one relationship between the two columns; neither contains any duplicated entries. That's why the example is trivial. For a non-trivial use consider a database of classical music in which we have fields COMP and WORK which hold the composer's name and the work's title for each record. Suppose COMP is linked to column 0 of a validation table which has three additional columns containing Nationality, and Birth and Death dates for each composer. Each of columns 1-3 would contain entries repeated several times - probably many times in the case of Nationality. Can we find out quickly which works (if any) were written by French composers born in 1880? Yes, by using the formula:

And which composers have a centenary or bicentenary in 2004?

which will, admittedly, list every work by composers who were born, or died, in 1804 or 1904 so the same composer might make many appearances in the report. Does it really matter?

Currency fields

Previous versions of Powerbase had no convenient way of specifying currency units. Provided no calculation on a field was required you could, of course, use an Unrestricted type and include the £, $ or ¤ (Euro) symbol within the field. Most currency fields do participate in calculations, however, and must therefore be defined as Numeric (fixed point, 2 places). The only option then was to place the currency symbol in the descriptor, which isn't very satisfactory since it won't appear in reports.

v.9.20 handles this by providing radio buttons which may be selected to prefix the number in a fixed point field with the required currency symbol. Calculations on such fields work correctly and, since the currency symbol is part of the field content, it appears in reports.

New field types

Two new field types, Capitalise first and Capitalise each have been added to the Editable class. The first of these simply forces the initial letter of the field to uppercase and the remainder to lowercase. The second is perhaps more useful. Left to itself it forces the initial letter of each word in the field to uppercase. This might not be quite what you want; in titles etc. it is usual to capitalise each word with the exception of relatively insignificant words such as definite and indefinite articles, conjunctions, prepositions etc. This is taken care of by means of a comma-separated list, e.g. "the,a,an,of,to,for" which is held in a text file called CaseExcept and placed in the database directory.

Even with a CaseExcept file you will occasionally encounter words which you want to be left "as is". This can be achieved by preceding them with a hard space (Alt-space, character 160).

Derek Haslam